package com.innazis.jfrogsurvey.managers;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.innazis.jfrogsurvey.DAL.DBHelper;
import com.innazis.jfrogsurvey.config.Constants;
import com.innazis.jfrogsurvey.obj.AnswerObj;
import com.innazis.jfrogsurvey.obj.QuestionObj;
import com.innazis.jfrogsurvey.obj.ServerResponse;
import com.innazis.jfrogsurvey.obj.SurveyObj;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

/**
 * Created by Inna on 06.06.14.
 */
public class SurveyManager {
    private DBHelper DBHelper;

    // Creates the helper, the DB connection
    public SurveyManager(Context context) {
        DBHelper = new DBHelper(context);
    }

// pull out the data
    public ArrayList<AnswerObj> getAnswers (QuestionObj questionObj) {
        ArrayList<AnswerObj> lstAnswers = new ArrayList<AnswerObj>();
        SQLiteDatabase db = DBHelper.getReadableDatabase();

        Cursor answCursor = db.rawQuery("SELECT * FROM " + Constants.TBL_ANSWERS
                + " WHERE " + Constants.survey_id_p  + " = " + questionObj.get_survey_id()
                + " AND " + Constants.question_id_p + " = " + questionObj.get_question_id(), null);
        while (answCursor.moveToNext()) {
            lstAnswers.add(convertCursorToAnswerObj(answCursor));
        }
        answCursor.close();
        db.close();

        return (lstAnswers);
    }

    public ArrayList<QuestionObj> getQuestions (SurveyObj surveyObj) {
        ArrayList<QuestionObj> lstQuestions = new ArrayList<QuestionObj>();
        SQLiteDatabase db = DBHelper.getReadableDatabase();

        Cursor quesCursor = db.rawQuery("SELECT * FROM " + Constants.TBL_QUESTIONS
                + " WHERE " + Constants.survey_id_p + " = " + surveyObj.get_survey_id(), null);

        while (quesCursor.moveToNext()) {
            QuestionObj temp = convertCursorToQuestionObj(quesCursor);
            temp.set_answers_list(getAnswers(temp));
            int count = temp.get_answers_list().size();
            if (count != temp.get_answers_num()) {
                temp.set_answers_num(count);
            }
            lstQuestions.add(temp);
        }
        quesCursor.close();
        db.close();

        return (lstQuestions);
    }

    public SurveyObj getSurvey (int _survey_id) {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        SurveyObj surveyObj = new SurveyObj();
        Cursor survCursor = db.rawQuery("SELECT * FROM " + Constants.TBL_SURVEYS
                + " WHERE " + Constants.survey_id_p + " = " + _survey_id, null);

        while (survCursor.moveToNext()) {
            surveyObj = convertCursorToSurveyObj(survCursor);
            surveyObj.set_questions_list(getQuestions(surveyObj));
            int count = surveyObj.get_questions_list().size();
            if (count != surveyObj.get_questions_num()) {
                surveyObj.set_questions_num(count);
            }
        }
        survCursor.close();
        db.close();

        return (surveyObj);
    }

    public ArrayList<SurveyObj> getAllSurveys () {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        ArrayList<SurveyObj> lstAllSurveys = new ArrayList<SurveyObj>();
        Cursor survCursor = db.rawQuery("SELECT * FROM " + Constants.TBL_SURVEYS
                + " WHERE " + Constants.survey_id_p + " <> 0 ", null);

        while (survCursor.moveToNext()) {
            SurveyObj temp = convertCursorToSurveyObj(survCursor);
            temp.set_questions_list(getQuestions(temp));
            int count = temp.get_questions_list().size();
            if (count != temp.get_questions_num()) {
                temp.set_questions_num(count);
            }
            lstAllSurveys.add(temp);
        }
        survCursor.close();
        db.close();

        return (lstAllSurveys);
    }

    public ArrayList<SurveyObj> getNotSentSurveys () {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        ArrayList<SurveyObj> lstAllSurveys = new ArrayList<SurveyObj>();
        Cursor survCursor = db.rawQuery("SELECT * FROM " + Constants.TBL_SURVEYS
                + " WHERE " + Constants.is_sent_p + " = 0 AND "
                + Constants.survey_id_p + " <> 0 ", null);

        while (survCursor.moveToNext()) {
            SurveyObj temp = convertCursorToSurveyObj(survCursor);
            temp.set_questions_list(getQuestions(temp));
            int count = temp.get_questions_list().size();
            if (count != temp.get_questions_num()) {
                temp.set_questions_num(count);
            }
            lstAllSurveys.add(temp);
        }
        survCursor.close();
        db.close();

        return (lstAllSurveys);
    }

    public int notSentSurveys() {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        int count = 0;
        Cursor mCount = db.rawQuery("SELECT COUNT(*) FROM "
                + Constants.TBL_SURVEYS + " WHERE " + Constants.is_sent_p + " = 0 AND "
                + Constants.survey_id_p + " <> 0 ", null);
        mCount.moveToFirst();
        count = mCount.getInt(0);
        db.close();
        return  count;
    }

    public int getTodaysCounter() {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        int count = 0;
        SimpleDateFormat parser = new SimpleDateFormat("dd-MM-yyyy");
        Calendar calendar = new GregorianCalendar();
        int day = calendar.get( Calendar.DATE );
        int month = calendar.get( Calendar.MONTH) + 1;
        int year = calendar.get(Calendar.YEAR);
        String todayStr = String.valueOf(day) + "-" + String.valueOf(month) + "-" + String.valueOf(year);
        Date today = new Date();
        try {
            today = parser.parse(todayStr);
        } catch (ParseException e) {
            // Invalid date was entered
        }
        Cursor survCursor = db.rawQuery("SELECT " + Constants.submitted_datetime_p + " FROM " + Constants.TBL_SURVEYS
                + " WHERE " + Constants.survey_id_p + " <> 0 ", null);

        while (survCursor.moveToNext()) {
            Date _submitted_datetime = new Date(survCursor.getLong(0));
            SimpleDateFormat dateformat = new SimpleDateFormat(
                    "dd-MM-yyyy");
        try {
            Date userDate = parser.parse(dateformat.format(_submitted_datetime));
            if (!userDate.before(today)) {
                count++;
            }
        } catch (ParseException e) {
            // Invalid date was entered
        }
        }
        return count;
    }

    public int getWholePeriodCounter() {
        SQLiteDatabase db = DBHelper.getReadableDatabase();

        Cursor mCount = db.rawQuery("select count(*) from "
                + Constants.TBL_SURVEYS + " where " +  Constants.survey_id_p + " <> 0 ", null);
        mCount.moveToFirst();
        int count = mCount.getInt(0);
        mCount.close();
        return count;
    }

// insert the data
    //start insert data
    public ServerResponse addSurvey (SurveyObj surveyObj) {
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        ServerResponse response = new ServerResponse(-1,"Survey " + surveyObj.get_survey_id() + " was updated.");
        if(verification(Constants.survey_id_p, String.valueOf(surveyObj.get_survey_id()), Constants.TBL_SURVEYS, db)) {
            response.set_message("There was another survey in a local DB with the same survey_id: " + surveyObj.get_survey_id() + ". Deleted and updated");
            deleteSurvey(surveyObj);
        }
        int count = 0;
        for(QuestionObj item : surveyObj.get_questions_list()) {
            if (addQuestion(item)){
                count++;
            }
        }
        if (count != surveyObj.get_questions_num()) {
            surveyObj.set_questions_num(count);
        }
        ContentValues values = new ContentValues();

        values.put(Constants.survey_id_p, surveyObj.get_survey_id());
        values.put(Constants.survey_name_p, surveyObj.get_survey_name());
        values.put(Constants.survey_title_p, surveyObj.get_survey_title());
        values.put(Constants.questions_num_p, surveyObj.get_questions_num());
        values.put(Constants.is_sent_p, 0);
        Date datetime = new Date();
        values.put(Constants.submitted_datetime_p, datetime.getTime());
        values.put(Constants.user_data_p, surveyObj.get_user_data());
        try {
            response.set_return_code((int)db.insert(Constants.TBL_SURVEYS, null, values));
        }catch (Exception e){
            e.printStackTrace();
        }
        db.close();
        return  response;
    }

    private boolean addQuestion (QuestionObj questionObj) {
        boolean res = false;
        SQLiteDatabase dbQ = DBHelper.getWritableDatabase();
        int count = 0;
        for(AnswerObj item : questionObj.get_answers_list()) {
            if (addAnswer(item)){
                count++;
            }
        }
        if (count != questionObj.get_answers_num()) {
            questionObj.set_answers_num(count);
        }
        ContentValues values = new ContentValues();
        values.put(Constants.question_id_p, questionObj.get_question_id());
        values.put(Constants.survey_id_p, questionObj.get_survey_id());
        values.put(Constants.question_title_p, questionObj.get_question_title());
        values.put(Constants.question_order_p, questionObj.get_question_order());
        values.put(Constants.answers_num_p, questionObj.get_answers_num());

        try {
            if (verification(Constants.question_id_p, Constants.survey_id_p,
                    String.valueOf(questionObj.get_question_id()),
                    String.valueOf(questionObj.get_survey_id()),Constants.TBL_QUESTIONS, dbQ)) {
                deleteQuestion(questionObj);
            }
            res = (dbQ.insert(Constants.TBL_QUESTIONS, null, values)) == 1 ? true : false;
        }catch (Exception e){
            e.printStackTrace();
        }
        //dbQ.close();
        return  res;
    }

    private boolean addAnswer (AnswerObj answerObj) {
        boolean res = false;
        SQLiteDatabase dbA = DBHelper.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Constants.answer_id_p, answerObj.get_answer_id());
        values.put(Constants.question_id_p, answerObj.get_question_id());
        values.put(Constants.survey_id_p, answerObj.get_survey_id());
        values.put(Constants.answer_order_p, answerObj.get_answer_order());
        values.put(Constants.answer_text_p, answerObj.get_answer_text());
        values.put(Constants.object_name_p, String.valueOf(answerObj.get_object_name()));
        values.put(Constants.is_checked_p, 0);
        try {
            if (verification(Constants.answer_id_p, Constants.question_id_p,Constants.survey_id_p,
                    String.valueOf(answerObj.get_answer_id()),String.valueOf(answerObj.get_question_id()),
                    String.valueOf(answerObj.get_survey_id()),Constants.TBL_ANSWERS, dbA)) {
                deleteAnswer(answerObj);
            }
            res = (dbA.insert(Constants.TBL_ANSWERS, null, values)) == 1 ? true : false;
        } catch (Exception e){
            e.printStackTrace();
        }
        //dbA.close();
        return  res;
    }

    public SurveyObj copySurvey(String user_data) {
        SurveyObj copySurvey = new SurveyObj();
        try {
            copySurvey = getSurvey(0);
            SQLiteDatabase db = DBHelper.getReadableDatabase();
            Cursor mCount = db.rawQuery("select count(*) from "
                    + Constants.TBL_SURVEYS, null);
            mCount.moveToFirst();
            int new_survey_id = mCount.getInt(0);
            mCount.close();

            // add a new survey
            for (int count = 0; count < Constants.MAX_ATTEMPTS; count ++) {
                // create a new survey object with a new survey id
                copySurvey.set_survey_id(new_survey_id);
                copySurvey.set_user_data(user_data);
                for(QuestionObj questionObj : copySurvey.get_questions_list()){
                    questionObj.set_survey_id(new_survey_id);
                    for (AnswerObj answerObj : questionObj.get_answers_list()) {
                        answerObj.set_survey_id(new_survey_id);
                    }
                }

                ServerResponse response = addSurvey(copySurvey);
                if (response.get_return_code() > 0) {
                    count = Constants.MAX_ATTEMPTS;
                }
                new_survey_id++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return copySurvey;
    }

    //end insert data

    public boolean updateSurveyValues (SurveyObj surveyObj) {
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        boolean res = false;
        int count = 0;
        try {
        for(QuestionObj obj : surveyObj.get_questions_list()) {
            for (AnswerObj item : obj.get_answers_list()) {
                ContentValues values = new ContentValues();
                values.put(Constants.is_checked_p, (item.is_is_checked() == true) ? 1 : 0);
                int row = db.update(Constants.TBL_ANSWERS, values, Constants.survey_id_p + " = " + item.get_survey_id()
                        + " and " + Constants.question_id_p + " = " + item.get_question_id()
                        + " and " + Constants.answer_id_p + " = " + item.get_answer_id(), null);
                res = row > 0;
            }
        }
        Date datetime = new Date();
        ContentValues values = new ContentValues();
        values.put(Constants.submitted_datetime_p, datetime.getTime());
        values.put(Constants.is_sent_p, 0);
        db.update(Constants.TBL_SURVEYS, values, Constants.survey_id_p + "="
        + surveyObj.get_survey_id(), null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        db.close();
        return res;
    }

    public boolean updateSentStatus (SurveyObj surveyObj){
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        boolean res = false;
        try {
        Date datetime = new Date();
        ContentValues values = new ContentValues();
        values.put(Constants.submitted_datetime_p, datetime.getTime());
            values.put(Constants.is_sent_p, (surveyObj.get_is_sent() == true) ? 1 : 0);
        db.update(Constants.TBL_SURVEYS, values, Constants.survey_id_p + "="
                + surveyObj.get_survey_id(), null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    db.close();
    return res;
    }
// delete

    public void deleteAllSurveys () {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        try {
            int res = db.delete(Constants.TBL_ANSWERS, "1", null);
            res = db.delete(Constants.TBL_QUESTIONS, "1", null);
            res = db.delete(Constants.TBL_SURVEYS, "1", null);
        }catch (Exception e){
            e.printStackTrace();
        }
        db.close();
    }

    public void deleteSurvey (SurveyObj surveyObj) {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        try {
            db.delete(Constants.TBL_ANSWERS, Constants.survey_id_p + "=" + surveyObj.get_survey_id(), null);
            db.delete(Constants.TBL_QUESTIONS, Constants.survey_id_p + "=" + surveyObj.get_survey_id(), null);
            db.delete(Constants.TBL_SURVEYS, Constants.survey_id_p + "=" + surveyObj.get_survey_id(), null);
        }catch (Exception e){
            e.printStackTrace();
        }
        db.close();
    }

    private void deleteQuestion (QuestionObj questionObj) {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        try {
            db.delete(Constants.TBL_ANSWERS, Constants.survey_id_p + "=" + questionObj.get_survey_id()
                    + " AND " + Constants.question_id_p + "=" + questionObj.get_question_id(), null);
            db.delete(Constants.TBL_QUESTIONS, Constants.survey_id_p + "=" + questionObj.get_survey_id()
                    + " AND " + Constants.question_id_p + "=" + questionObj.get_question_id(), null);
        }catch (Exception e){
            e.printStackTrace();
        }
        //db.close();
    }

    private void deleteAnswer (AnswerObj answerObj) {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        try {
            db.delete(Constants.TBL_ANSWERS, Constants.survey_id_p + "=" + answerObj.get_survey_id()
                    + " AND " + Constants.question_id_p + "=" + answerObj.get_question_id()
                    + " AND " + Constants.answer_id_p + "=" + answerObj.get_answer_id(), null);;
        }catch (Exception e){
            e.printStackTrace();
        }
        //db.close();
    }

// cursor convertation
    // start cursor convertation
    private AnswerObj convertCursorToAnswerObj (Cursor answCursor) {
        int _answer_id = answCursor.getInt(0);
        int _question_id = answCursor.getInt(1);
        int _survey_id = answCursor.getInt(2);
        int _answer_order = answCursor.getInt(3);
        String _answer_text = answCursor.getString(4);
        Object _object_name = answCursor.getString(5);
        boolean _is_checked = (answCursor.getInt(6) == 1) ? true : false;

        AnswerObj answerObj = new AnswerObj(_answer_id, _question_id, _survey_id,
                _answer_order,_answer_text, _object_name, _is_checked);
        return  answerObj;
    }

    private QuestionObj convertCursorToQuestionObj (Cursor quesCursor) {
        int _question_id = quesCursor.getInt(0);
        int _survey_id = quesCursor.getInt(1);
        String _question_title = quesCursor.getString(2);
        int _question_order = quesCursor.getInt(3);
        int _answers_num = quesCursor.getInt(4);

        QuestionObj questionObj = new QuestionObj(_question_id, _survey_id, _question_title,
                                    _question_order, _answers_num,null);
        return  questionObj;
    }

    private SurveyObj convertCursorToSurveyObj (Cursor survCursor) {
        int _survey_id = survCursor.getInt(0);
        String _survey_name = survCursor.getString(1);
        String _survey_title = survCursor.getString(2);
        int _questions_num = survCursor.getInt(3);
        boolean _is_sent = (survCursor.getInt(4) == 1) ? true : false;
        Date _submitted_datetime = new Date(survCursor.getLong(5));
        String _user_data = (survCursor.getString(6));
        SurveyObj surveyObj = new SurveyObj(_survey_id, _survey_name, _survey_title,
                                 _questions_num, _is_sent, _submitted_datetime, null, _user_data);
        return  surveyObj;
    }
    //end cursor convertation

// verification
    // verification

    public int surveyVerification(String _key, String _value, String _table) {
        int result = -1;
        Log.d("CheckExist", "Check exist with id : " + _value);
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        Cursor c = db.rawQuery("SELECT " + Constants.survey_id_p + " FROM " + _table + " WHERE " + _key
                + "=?", new String[] { _value });
        while (c.moveToNext()) {
            result = c.getInt(0);
        }
        c.close();
        return result;
    }

    public boolean verification(String _key, String _value, String _table,
                                SQLiteDatabase db) {
        Log.d("CheckExist", "Check exist with id : " + _value);
        Cursor c = db.rawQuery("SELECT 1 FROM " + _table + " WHERE " + _key
                + "=?", new String[] { _value });
        boolean exists = c.moveToFirst();
        c.close();
        return exists;
    }

    public boolean verification(String _key1, String _key2, String _value1, String _value2, String _table,
                                SQLiteDatabase db) {
        Cursor c = db.rawQuery("SELECT 1 FROM " + _table + " WHERE " + _key1
                + "=" + _value1 + " AND " + _key2 + "=" + _value2, null);
        boolean exists = c.moveToFirst();
        c.close();
        return exists;
    }

    public boolean verification(String _key1, String _key2, String _key3, String _value1,
                                String _value2, String _value3, String _table, SQLiteDatabase db) {
        Cursor c = db.rawQuery("SELECT 1 FROM " + _table + " WHERE " + _key1 + "=" + _value1
                + " AND " + _key2 + "=" + _value2 + " AND " + _key3 + "=" + _value3, null);
        boolean exists = c.moveToFirst();
        c.close();
        return exists;
    }
    // end verification

}
